<?php
namespace app\index\controller;
use app\index\model\Score as ScoreModel;
use app\index\controller\Common;
use think\Db;
use PHPExcel_IOFactory;
use PHPExcel;
// use app\index\controller\Common;
// 成绩控制器
// *   @author 于江 2017年3月30日
// *   邮箱935315164@qq.com
class Score extends Common
{
	// 后台首页
    public function index()
    {

        $stures=Db::name('students')->alias('a')->join('scoretable b','a.sid = b.students_sid')->order('number desc')->paginate(10);
        $this->assign('stures',$stures);
		return $this->fetch();
    }
    // 添加
    public function add()
    {
        if (request()->isPost()) {
          
            // Db::transaction(function(){
            $data=array(
                'sid'=>input('id'),
                'sname'=>input('sname'),
                'class'=>input('class'),
                'number'=>input('number'),
                'xuehao'=>input('xuehao'),
                ); 
             $userId=Db::name('students')->insertGetId($data);
             $score=array(
                'students_sid'=>$userId,
                'chinese'=>input('chinese'),
                'mathematics'=>input('mathematics'),
                'english'=>input('english'),
                'physical'=>input('physical'),
                'chemical'=>input('chemical'),
                'biology'=>input('biology'),
                'historical'=>input('historical'),
                'geography'=>input('geography'),
                'politics'=>input('politics'),
                'grades'=>input('grades'),
                'rnking'=>input('rnking'),
                'county'=>input('county'),
                'total'=>input('total'),
                'average'=>input('average'),
                );
                $add=Db::name('scoretable')->insert($score);
                // });
            $stures=array(
                'stu'=>$data,
                'score'=>$score,
                );
            // var_dump($stures);die;
             $validate = \think\Loader::validate('Score');//自动加载验证规则

            if(!$validate->scene('add')->check($data)){
                $this->error($validate->getError());
            }
             if($stures){
                $this->success('添加成绩成功！',url('index'));
            }else{
                $this->error('添加成绩失败！');
            }
            return;
        }
        return $this->fetch();
    }
    // 编辑
    public function edit(){
            
    if (request()->isPost()) {

            $data=array(
                'sname'=>input('sname'),
                'class'=>input('class'),
                'number'=>input('number'),
                'xuehao'=>input('xuehao'),
                ); 
             $stus=db('students')->where('sid',input('id'))->update($data);
             $score=array(
                'chinese'=>input('chinese'),
                'mathematics'=>input('mathematics'),
                'english'=>input('english'),
                'physical'=>input('physical'),
                'chemical'=>input('chemical'),
                'biology'=>input('biology'),
                'historical'=>input('historical'),
                'geography'=>input('geography'),
                'politics'=>input('politics'),
                'grades'=>input('grades'),
                'rnking'=>input('rnking'),
                'county'=>input('county'),
                'total'=>input('total'),
                'average'=>input('average'),
                );
                $add=db('scoretable')->where('students_sid',input('id'))->update($score);
                // });
            $stures=array(
                'stu'=>$stus,
                'score'=>$score,
                );
            // var_dump($stures);die;
             $validate = \think\Loader::validate('Score');//自动加载验证规则

            if(!$validate->scene('edit')->check($data)){
                $this->error($validate->getError());
            }
             if($stures){
                $this->success('修改成绩成功！',url('index'));
            }else{
                $this->error('修改成绩失败！');
            }

           return;
        }
      
        $sid=input('id');//当前id
        $stures=db('students')->where('sid',$sid)->find();//学生表
        $score=db('scoretable')->where('students_sid',$sid)->find();//成绩表
        $this->assign(
                array(
                    'stures'=>$stures,
                    'score'=>$score,
                )
            );
        return $this->fetch();
    }
    // 删除
     public function del(){
        $id=input('id');
        $stus=db('students')->delete($id);
        $scores=db('scoretable')->where('students_sid',$id)->delete();
        $del=array(
                'stu'=>$stus,
                'score'=>$scores,
            );

        if($del){
            $this->success('删除学生成绩成功！',url('index'));
        }else{
            $this->error('删除学生成绩失败！');
        }
    }
    // 显示导入的模板
    public function excel(){
        return $this->fetch();
    }
public function impUser(){
      vendor("PHPExcel.PHPExcel");
      $objPHPExcel = new PHPExcel();
        if (! empty ( $_FILES ['file1'] ['name'] ))  
     {  
        // var_dump($_FILES);die;
              $tmp_file = $_FILES ['file1'] ['tmp_name'];  
              $file_types = explode ( ".", $_FILES ['file1'] ['name'] );  
              $file_type = $file_types [count ( $file_types ) - 1];  
           /*判别是不是.xls文件，判别是不是excel文件*/  
           if (strtolower ( $file_type ) != "xls"&&strtolower ( $file_type ) != "xlsx")        
          {  
             $this->error ( '不是Excel文件，重新上传' );  
           }  
          /*设置上传路径*/  
           $savePath = ROOT_PATH. '/public/excel/';  
          /*以时间来命名上传的文件*/  
           $str = date ( 'Ymdhis' );  
           $file_name = $str . "." . $file_type;  
           /*是否上传成功*/  
           if (! copy ( $tmp_file, $savePath . $file_name ))  
           {  
             $this->error ( '上传失败' );  
           }
           // else{
           //   $this->success ( '上传成功' );  
           // }  
          /* 
        *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中 
      
       注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入 
      */  
         // $res =  $objPHPExcel->read ( $savePath . $file_name );  
        
      /* 
        重要代码 解决Thinkphp M、D方法不能调用的问题  
        如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码 
      */  
      //spl_autoload_register ( array ('Think', 'autoload' ) );  
      /*对生成的数组进行数据库的写入*/  
  }
        vendor("PHPExcel.PHPExcel"); 
        $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
        if($extension == 'xlsx') {
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            // var_dump($objReader);
            $objPHPExcel =$objReader->load($savePath.$file_name, $encode = 'utf-8');
        }else if($extension == 'xls'){
            $objReader =\PHPExcel_IOFactory::createReader('Excel5');
            $objPHPExcel =$objReader->load($savePath.$file_name, $encode = 'utf-8');
        }
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();//取得总行数
        $highestColumn = $sheet->getHighestColumn(); //取得总列数
        //$arrexcel = $objPHPExcel->getSheet(0)->toarray(0);//excel表数据信息
        for($i = 2; $i <= $highestRow; $i++){
            $score=array();
            $score['sname'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
            $score['xuehao'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
            $score['class'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
            $score['number'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
            // $addres=Db::name('students')->insert($score);//插入学生表
            $userId=Db::name('students')->insertGetId($score);
            $data['chinese'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
            $data['mathematics'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
            $data['english'] = $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
            $data['physical'] = $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
            $data['chemical'] = $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
            $data['biology'] = $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
            $data['politics'] = $objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();
            $data['historical'] = $objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();
            $data['geography'] = $objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
            $data['grades'] = $objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue();
            $data['rnking'] = $objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
            $data['county'] = $objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue();
            $data['total'] = $objPHPExcel->getActiveSheet()->getCell("Q".$i)->getValue();
            $data['average'] = $objPHPExcel->getActiveSheet()->getCell("R".$i)->getValue();
            // $score['sid'] = $objPHPExcel->getActiveSheet()->getCell("S".$i)->getValue();    
            $data['students_sid'] =$userId;
             $stus=Db::name('scoretable')->insert($data);//插入成绩表
            

            $info =array(
                'score'=>$score,
                'data'=>$data
                );
            
        }
        if($info){
             $this->success('导入成功！',url('score/index'));
        }else{
            $this->error('导入失败 ！');
        }    
         
 
    
}


    /**导出方法**/

public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");
       
        $objPHPExcel = new PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
       // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
        } 
          // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
          for($j=0;$j<$cellNum;$j++){

            // var_dump($expCellName[$i],$expTableData,$expTableData[$i]);die;
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
          }             
        }   
        
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
        $objWriter->save('php://output'); 
        exit;   
    }
/**
     *
     * 导出Excel
     */
    function expUser(){//导出Excel
        $xlsName  = "成绩表";
        $xlsCell  = array(
        array('sid','序号'),
        array('sname','姓名'),
        array('xuehao','学号'),
        array('class','班级'),
        array('number','年级'),
        array('chinese','语文'),
        array('mathematics','数学'),
        array('english','英语'),
        array('physical','物理'),
        array('chemical','化学'),
        array('biology','生物'),
        array('politics','政治'),
        array('historical','历史'),
        array('geography','地理'),
        array('grades','班级名次'),
        array('rnking','年级名次'),
        array('county','旗级名次'),
        array('total','总分'),
        array('average','平均分')     
        );
        $xlsData= Db::name('students')->alias('a')->join('scoretable b','a.sid = b.students_sid')->Field('sid,sname,xuehao,class,number,chinese,mathematics,english,physical,chemical,chemical,biology,politics,historical,geography,grades,rnking,county,total,average')->select();
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
         
    }









}


